create or replace package body cust_hsd_admission as

function grade_on_day(p_pupil_number number, p_date date) 
return varchar2
is
l_return varchar2(2);
begin
   begin
      select
         distinct
         last_value(r.grade_promoted_to) over (partition by r.pupil_number order by r.promotion_date, r.id rows between unbounded preceding and unbounded following)
            into l_return
      from admassist.homeform_history r
      where 1=1
         and r.pupil_number = p_pupil_number
         and trunc(r.promotion_date) <= p_date
      ;
   EXCEPTION WHEN NO_DATA_FOUND THEN
      l_return := null;
   end;
   if l_return is null then
      select
         distinct
         last_value(r.current_grade_placement) over (partition by r.pupil_number order by r.registration_date, r.id rows between unbounded preceding and unbounded following)
            into l_return
      from cust_hsd_v_registrations_all r
      where 1=1
         and r.pupil_number = p_pupil_number
         and trunc(r.registration_date) <= p_date
      ;
   end if;
   return l_return;
end;

function home_sch_on_day(p_pupil_number number, p_date date) 
return number 
is
l_return number(9);
begin
   begin
      select aw.admit_school into l_return
      from cust_hsd_v_aw_key aw
      where 1=1
         and aw.pupil_number = p_pupil_number
         and aw.enr_type = 'h'
         and trunc(p_date) between trunc(aw.admit_date) and (nvl(trunc(aw.wd_date),sysdate) - interval '1' day)
      ;
   EXCEPTION WHEN NO_DATA_FOUND THEN
      l_return := null;
   end;
   if l_return is null then
      --get last home school:
      select distinct last_value(aw.admit_school) over (partition by aw.pupil_number order by aw.effective_date rows between unbounded preceding and unbounded following)
         into l_return
      from admassist.admission_withdraw aw
      where 1=1
         and aw.pupil_number = p_pupil_number
         and aw.admit_withdraw_ind = 'W'
         and trunc(aw.effective_date) - interval '1' day < trunc(p_date)
      ;
   end if;
   return l_return;
end;

function reg_in_period(p_pupil_number number, p_school number, p_start_date date, p_end_date date) 
return varchar2
is
l_return varchar2(1);
begin
   select
      case
         when exists
         (
            select 1 from 
            (
               select r1.pupil_number, r1.school, r1.registration_date, r1.transaction_code from registrations r1
               UNION ALL
               select r2.pupil_number, r2.school, r2.registration_date, r2.transaction_code from registrations_archive r2
            ) r
            where 1=1
               and r.pupil_number = p_pupil_number
               and r.school = p_school
               and trunc(r.registration_date) between trunc(p_start_date) and trunc(p_end_date)
               and r.transaction_code in ('I-ADM','E-ADM')
         ) then 'Y'
         else 'N'
      end into l_return
   from dual;
   return l_return;
end;

function reg_on_day(p_pupil_number number, p_school number, p_date date) 
return varchar2
is
l_return varchar2(1);
begin
   select
      case
         when exists
         (
            select 1 from admassist.cust_hsd_v_aw_key aw
            where 1=1
               and aw.pupil_number = p_pupil_number
               and aw.admit_school = p_school
               and aw.enr_type = 'h'
               and trunc(p_date) between trunc(aw.admit_date) and trunc(nvl(aw.wd_date,p_date))
               and reg_in_period(p_pupil_number,p_school,aw.admit_date,nvl(aw.wd_date,p_date)) = 'Y'
         ) then 'Y'
         else 'N'
      end into l_return
   from dual;
   return l_return;
end;

end; --package
/
